cls
clear all
set matsize 11000

********************************************************************************

********************************************************************************

	**commodity flow survey
	qui import delimited "raw/tradeflows/cfs_2012_pumf.txt",clear 
	keep orig_state dest_state orig_ma dest_ma naics shipmt_value shipmt_wght wgt_factor mode export_*
	order orig_state dest_state orig_ma dest_ma naics shipmt_value shipmt_wght wgt_factor mode export_*
	
		*->drop if "Alaska" or "Hawaii" are origin/destination
		qui drop if orig_state==2 | dest_state==2 | orig_state==15 | dest_state==15
		
		*->keep only "known" shipments
		rename (orig_state dest_state orig_ma dest_ma) (originstate destinstate originmsa destinmsa)
		qui replace originmsa = 99999 if originmsa==0 & originstate!=0
		qui drop if originmsa==0 & originstate==0
		qui replace originmsa = originstate if originmsa==9999
		qui replace destinmsa = destinstate if destinmsa==9999
		
		*->weight shipment value
		qui g shipmntvalue = shipmt_value*wgt_factor 

		*->assign industries
		
		qui g industryname = ""
		qui g industrycode = .
		
			*311 313 325 326 331 333 334 336 337 / 311 313 321 324 325 326 327 331 333 334 336 337
			***Food, Beverage, and Tobacco Products (NAICS 311-312)
			qui replace industrycode = 311 if naics==311 | naics==312
			qui replace industryname = "Food, Beverage, and Tobacco Products" if industrycode==311
		
			***Textile, Textile Product Mills, Apparel, Leather, and Allied Products (NAICS 313-316)
			qui replace industrycode = 313 if naics==313 | naics==314 | naics==315 | naics==316
			qui replace industryname = "Textile, Textile Product Mills, Apparel, Leather, and Allied Products" if industrycode==313
			
			***Wood Products, Paper, Printing, and Related Support Activities (NAICS 321-323)
			qui replace industrycode = 321 if naics==321 | naics==322 | naics==323
			qui replace industryname = "Wood Products, Paper, Printing, and Related Support Activities" if industrycode==321
		
			***Petroleum and Coal Products (NAICS 324)
			qui replace industrycode = 324 if naics==324
			qui replace industryname = "Petroleum and Coal Products" if industrycode==324
			
			***Chemical (NAICS 325)
			qui replace industrycode = 325 if naics==325			
			qui replace industryname = "Chemical" if industrycode==325
			
			***Plastics and Rubber Products (NAICS 326)
			qui replace industrycode = 326 if naics==326	
			qui replace industryname = "Plastics and Rubber Products" if industrycode==326
			
			***Nonmetallic Mineral Products (NAICS 327)
			qui replace industrycode = 327 if naics==327
			qui replace industryname = "Nonmetallic Mineral Products" if industrycode==327
					
			***Primary Metal and Fabricated Metal Products (NAICS 331-332)
			qui replace industrycode = 331 if naics==331 | naics==332
			qui replace industryname = "Primary Metal and Fabricated Metal Products" if industrycode==331
			
			***Machinery (NAICS 333)
			qui replace industrycode = 333 if naics==333
			qui replace industryname = "Machinery" if industrycode==333
			
			***Computer and Electronic Products, and Electrical Equipment and Appliance (NAICS 334-335)
			qui replace industrycode = 334 if naics==334 | naics==335
			qui replace industryname = "Computer and Electronic Products, and Electrical Equipment and Appliance" if industrycode==334
			
			***Transportation Equipment (NAICS 336)
			qui replace industrycode = 336 if naics==336
			qui replace industryname = "Transportation Equipment" if industrycode==336
			
			***Furniture and Related Products, and Miscellaneous Manufacturing (NAICS 337-339)
			qui replace industrycode = 337 if naics==337 | naics==338 | naics==339
			qui replace industryname = "Furniture and Related Products, and Miscellaneous Manufacturing" if industrycode==337
			
			***Transport Services (NAICS 481-488)
			qui replace industrycode = 481 if naics==481 | naics==482 | naics==483 | naics==484 | naics==485 | naics==486 | naics==487 | naics==488
			qui replace industryname = "Transport Services" if industrycode==481
			
			***Information Services (NAICS 511-518)
			qui replace industrycode = 511 if naics==511 | naics==512 | naics==513 | naics==514 | naics==515 | naics==516 | naics==517 | naics==518
			qui replace industryname = "Information Services" if industrycode==511
			
			***Finance and Insurance (NAICS 521-525)
			qui replace industrycode = 521 if naics==521 | naics==522 | naics==523 | naics==524 | naics==525
			qui replace industryname = "Finance and Insurance" if industrycode==521
			
			***Real Estate (NAICS 531-533)
			qui replace industrycode = 531 if naics==531 | naics==532 | naics==533
			qui replace industryname = "Real Estate" if industrycode==531
			
			***Education (NAICS 61)
			qui replace industrycode = 61 if naics==61
			qui replace industryname = "Education" if industrycode==61
			
			***Health Care (NAICS 621-624)
			qui replace industrycode = 621 if naics==621 | naics==622 | naics==623 | naics==624	
			qui replace industryname = "Health Care" if industrycode==621
			
			***Accommodation and Food Services (NAICS 721-722)
			qui replace industrycode = 721 if naics==721 | naics==722
			qui replace industryname = "Accommodation and Food Services" if industrycode==721
			
			***Other Services (NAICS 493, 541, 55, 561, 562, 711-713, 811-814)
			qui replace industrycode = 493 if naics==541 | naics==55 | naics==561 | naics==562 | naics==711 | naics==712 | naics==713 | naics==714 | naics==811 | naics==812 | naics==813 | naics==814
			qui replace industryname = "Other Services" if industrycode==493
			
			***Wholesale and Retail Trade sector (NAICS 42-45)
			qui replace industrycode = 42 if naics>=4200 & naics<=4600
			qui replace industryname = "Wholesale and Retail Trade sector" if industrycode==42
			
			***Construction 
			qui replace industrycode = 236 if naics==236
			qui replace industryname = "Construction" if industrycode==236
			
			**drop if industry unassigned
			qui drop if industrycode==.

		*->collapse by industry, domestic
	
			qui g shipmntwght = shipmt_wght*wgt_factor 
			qui collapse (sum) value=shipmntvalue weight=shipmntwght shipments=wgt_factor
			qui g avgvalue_per_shipment = value/shipments
			sum avgvalue_per_shipment
			keep  avgvalue_per_shipment
			outfile avgvalue_per_shipment using "data/tradeflows/avg_value_per_shipment.csv", comma
			